#!/nmsmw/python/bin/python
# -*- encoding: gbk -*-
#=========================================
# Filename : oracle_mul.py
# Filetype : Python
# Author   : Colben
# Create   : 2014-12-19 10:40:30
#=========================================

import os, sys, re, cx_Oracle, signal
from multiprocessing import Pool, cpu_count
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
#os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'

remote_ora = {}#<<FOLD<<
#remote_ora['gk']        = 'boya/boya@10.128.75.65:1521/orcl.ntars.com'
remote_ora['gk']        = 'ibms/ibms@10.166.64.41:1521/orcl'
remote_ora['108']       = 'boya/boya@10.166.64.41:1521/orcl'
remote_ora['anhui']	= 'boya/boya@10.152.64.33:1521/orcl.idevelopment.info'
remote_ora['beijing']	= 'boya/boya@10.134.64.33:1521/orcl.ntars.com'
remote_ora['chongqing']	= 'boya/boya@10.137.64.33:1521/orcl'
remote_ora['fujian']	= 'boya/boya@10.162.64.33:1521/orcl.ntars.com'
remote_ora['gansu']	= 'boya/boya@10.142.64.33:1521/orcl'
remote_ora['guangdong']	= 'boya/boya@10.164.64.33:1521/orcl.ntars.com'
remote_ora['guangxi']	= 'boya/boya@10.161.64.33:1521/orcl'
remote_ora['guizhou']	= 'boya/boya@10.157.64.33:1521/orcl'
remote_ora['hainan']	= 'boya/boya@10.160.64.33:1521/orcl'
remote_ora['hebei']	= 'boya/boya@10.141.64.33:1521/orcl.ntars.com'
remote_ora['heilong']	= 'boya/boya@10.146.64.33:1521/orcl.ntars.com'
remote_ora['henan']	= 'boya/boya@10.151.64.33:1521/orcl.ntars.com'
remote_ora['hubei']     = 'boya/boya@10.155.64.33:1521/orcl.ntars.com'
remote_ora['hunan']	= 'boya/boya@10.158.64.33:1521/orcl.ntars.com'
remote_ora['jiangsu']	= 'boya/boya@10.150.64.33:1521/orclXDB.ntars.com'
remote_ora['jiangxi']	= 'boya/boya@10.159.64.33:1521/orcl.info'
remote_ora['jilin']	= 'boya/boya@10.147.64.33:1521/orcl'
remote_ora['liaoning']	= 'boya/boya@10.143.64.33:1521/orcl.ntars.info'
remote_ora['neimeng']	= 'boya/boya@10.135.64.33:1521/orcl'
remote_ora['ningxia']   = 'boya3w/boya3w@10.139.64.33:1521/orcl'
remote_ora['qinghai']	= 'boya/boya@10.144.64.33:1521/orcl.ntars.com'
remote_ora['shandong']  = 'boya/boya@10.154.64.33:1521/orcl.ntars.com'
remote_ora['shanghai']	= 'boya/boya@10.163.64.33:1521/orcl.ntars.com'
remote_ora['shanxi']	= 'boya/boya@10.140.64.33:1521/orcl.ntars.com'
remote_ora['sanxi']	= 'boya/boya@10.145.64.33:1521/orcl.ntars.com'
remote_ora['sichuan']	= 'boya/boya@10.138.64.33:1521/orcl.ntars.com'
remote_ora['tianjin']	= 'boya/boya@10.148.64.33:1521/orcl'
remote_ora['xinjiang']	= 'boya/boya@10.149.64.33:1521/orcl.ntars.com'
remote_ora['xizang']	= 'boya/boya@10.136.64.33:1521/orcl.ntars.com'
remote_ora['yunnan']	= 'boya/boya@10.156.64.33:1521/orcl'
remote_ora['zhejiang']	= 'boya/boya@10.153.64.33:1521/orcl.ntars.com'

remote_rac = {}
#remote_rac['gk']        = 'boya/boya@10.128.75.67:1521/orcl.ntars.com'
remote_rac['anhui']	= 'boya/boya@10.152.64.34:1521/orcl.idevelopment.info'
remote_rac['beijing']	= 'boya/boya@10.134.64.34:1521/orcl.ntars.com'
remote_rac['chongqing']	= 'boya/boya@10.137.64.34:1521/orcl'
remote_rac['fujian']	= 'boya/boya@10.162.64.34:1521/orcl.ntars.com'
remote_rac['gansu']	= 'boya/boya@10.142.64.34:1521/orcl'
remote_rac['guangdong']	= 'boya/boya@10.164.64.34:1521/orcl.ntars.com'
remote_rac['guangxi']	= 'boya/boya@10.161.64.34:1521/orcl'
remote_rac['guizhou']	= 'boya/boya@10.157.64.34:1521/orcl'
remote_rac['hainan']	= 'boya/boya@10.160.64.34:1521/orcl'
remote_rac['hebei']	= 'boya/boya@10.141.64.34:1521/orcl.ntars.com'
remote_rac['heilong']	= 'boya/boya@10.146.64.34:1521/orcl.ntars.com'
remote_rac['henan']	= 'boya/boya@10.151.64.34:1521/orcl.ntars.com'
remote_rac['hubei']     = 'boya/boya@10.155.64.34:1521/orcl.ntars.com'
remote_rac['hunan']	= 'boya/boya@10.158.64.34:1521/orcl.ntars.com'
remote_rac['jiangsu']	= 'boya/boya@10.150.64.34:1521/orclXDB.ntars.com'
remote_rac['jiangxi']	= 'boya/boya@10.159.64.34:1521/orcl.info'
remote_rac['jilin']	= 'boya/boya@10.147.64.34:1521/orcl'
remote_rac['liaoning']	= 'boya/boya@10.143.64.34:1521/orcl.ntars.info'
remote_rac['neimeng']	= 'boya/boya@10.135.64.34:1521/orcl'
remote_rac['ningxia']   = 'boya3w/boya3w@10.139.64.34:1521/orcl'
remote_rac['qinghai']	= 'boya/boya@10.144.64.34:1521/orcl.ntars.com'
remote_rac['shandong']  = 'boya/boya@10.154.64.34:1521/orcl.ntars.com'
remote_rac['shanghai']	= 'boya/boya@10.163.64.34:1521/orcl.ntars.com'
remote_rac['shanxi']	= 'boya/boya@10.140.64.34:1521/orcl.ntars.com'
remote_rac['sanxi']	= 'boya/boya@10.145.64.34:1521/orcl.ntars.com'
remote_rac['sichuan']	= 'boya/boya@10.138.64.34:1521/orcl.ntars.com'
remote_rac['tianjin']	= 'boya/boya@10.148.64.34:1521/orcl'
remote_rac['xinjiang']	= 'boya/boya@10.149.64.34:1521/orcl.ntars.com'
remote_rac['xizang']	= 'boya/boya@10.136.64.34:1521/orcl.ntars.com'
remote_rac['yunnan']	= 'boya/boya@10.156.64.34:1521/orcl'
remote_rac['zhejiang']	= 'boya/boya@10.153.64.34:1521/orcl.ntars.com'
#>>FOLD>>

def close_dbs():#<<FOLD<<
    print 'Terminating db connections ...'
    for ora, db in accessed_dbs.items():
        try: db.close()
        except Exception, e:
            print 'Close', ora ,'failed:', e 
    sys.exit()
#>>FOLD>>

def handle_one_db(ora):#<<FOLD<<
    result = []
    cursor = accessed_dbs[ora].cursor()
    for sql in sql_list:
        try:
            cursor.execute(sql)
        except Exception, e:
            result.append((sql + ';', e.__class__.__name__ + ': ' + str(e).strip()))
            continue
        try:
            result.append((sql + ';', cursor.fetchall()))
        except cx_Oracle.InterfaceError:
            result.append((sql + ';', 'NULL'))
    cursor.close()
    return (ora, result)
#>>FOLD>>

def runsqls_on_alldbs():#<<FOLD<<
    process_pool = Pool(cpu_count())
    pool_return = process_pool.map_async(handle_one_db, accessed_dbs.keys())
    for ora, result in pool_return.get():
        result_fp.write('Database: ' + ora + '\n')
        for line in result:
            result_fp.write('Sql: ' + line[0] + '\n')
            if type(line[1]) is str:
                result_fp.write(line[1] + '\n')
            else:
                for record in line[1]:
                    result_fp.write(','.join(map(str, record)))
                    result_fp.write('\n')
        result_fp.write('\n')
    process_pool.close()
    process_pool.terminate()
    return
#>>FOLD>>

def get_fp(filename):#<<FOLD<<
    global result_fp
    result_fp = sys.stdout
    if '' != filename:
        try:
            result_fp = open(filename, 'a', 0)
        except:
            print 'Open', filename, 'failed, now print it stdout.'
    return
#>>FOLD>>

def get_sqls(sql):#<<FOLD<<
    global sql_list
    sql_list = []
    if '@' != sql[0]:
        sql_list.append(sql)
    else:
        try:
            sql_f = open(sql[1:].strip(), 'r')
            for line in sql_f:
                sql=line.strip().strip(';')
                if sql:
                    sql_list.append(sql)
            sql_f.close()
            if 0 == len(sql_list):
                return False
        except Exception, e:
            print e.__class__.__name__, ':', e
            return False
    return True
#>>FOLD>>

def input_sqls():#<<FOLD<<
    while True:
        sql = raw_input('\nSQL>> ')
        if re.match(r'exit.*', sql.lower()) or re.match(r'quit.*', sql.lower()):
            close_dbs()
        try:
            sql, filename = re.split(r';', sql, 1)
        except:
            print 'Wrong, missing ";" in the end.'
            continue
        if not get_sqls(sql.strip()):
            continue
        get_fp(filename.strip())
        runsqls_on_alldbs()
    return
#>>FOLD>>

def access_db(ora):#<<FOLD<<
    if ora not in remote_ora.keys():
        return (False, 'Unknown DB')
    try:
        db = cx_Oracle.connect(remote_ora[ora])
        db.autocommit = True
        return (True, db)
    except Exception, e:
        if ora not in remote_rac.keys():
            return (False, 'Access Failed: ' + str(e))
    	try:
    	    db = cx_Oracle.connect(remote_rac[ora])
    	    db.autocommit = True
            return (True, db)
    	except Exception, e:
            return (False, 'Access Failed: ' + str(e))
#>>FOLD>>

def check_dbs():#<<FOLD<<
    global accessed_dbs
    accessed_dbs = {}
    oras = remote_ora.keys()
    if 1 != len(sys.argv):
        oras = sys.argv[1:]
    for ora in oras:
        accessed, db = access_db(ora)
        if not accessed:
            print ora + ' : ' + db
            continue
        accessed_dbs[ora] = db
    if 0 == len(accessed_dbs):
        print 'Accessed no dbs, quit ...'
        sys.exit()
    return
#>>FOLD>>

def signal_quit(signum, frame):#<<FOLD<<
    print 'Got quit signal ...'
    close_dbs()
#>>FOLD>>

def main():#<<FOLD<<
    check_dbs()
    input_sqls()
    return
#>>FOLD>>

if '__main__' == __name__:
    signal.signal(2, signal_quit)
    #signal.signal(3, signal_quit)
    #signal.signal(15, signal_quit)
    main()

